﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Threading;
using DTO;

namespace DAO
{
    public class SimDAO
    {
        public static List<SimDTO> TimKiem(string soThueBao, float giaTu, float giaDen, int loaiSim, int nhaMang, ref int soLuong,  string run)
        {
            IsolationLevel iso;
            switch (run)
            {
                case "Unrepeatable read error":
                    iso = IsolationLevel.ReadCommitted;
                    break;
                case "Unrepeatable read fix":
                    iso = IsolationLevel.RepeatableRead;
                    break;
                default:
                    iso = IsolationLevel.ReadCommitted;
                    break;
            }
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlCount = "select count(s.MaSanPham) from Sim s join SanPham sp on sp.MaSanPham = s.MaSanPham  where sp.TinhTrang like N'Đang bán'";
            string sqlSelect = "select s.*, sp.DonGia, sp.MaNhaMang  from Sim s join SanPham sp on sp.MaSanPham = s.MaSanPham  where sp.TinhTrang like N'Đang bán'";
            string sqlWhere = "";
            if (soThueBao != "")
            {
                sqlWhere += " and (s.SoThueBao like '%" + soThueBao + "%' ) "; 
            }
            if (giaTu != -1)
            {
                sqlWhere += " and (sp.DonGia >= " + giaTu + ") ";
            }
            if (giaDen != -1)
            {
                sqlWhere += " and (sp.DonGia <= " + giaDen + ") ";
            }
            if (loaiSim != -1)
            {
                sqlWhere += " and (s.MaSanPham in (select MaSanPham from Sim_LoaiSim where MaLoaiSim = " + loaiSim + "))";
            }
            if (nhaMang != -1)
            {
                sqlWhere += " and (sp.MaNhaMang = " + nhaMang + ") ";
            }

            sqlCount += sqlWhere;
            sqlSelect += sqlWhere;
            connect.Open();
            SqlTransaction tran = connect.BeginTransaction(iso);
            SqlCommand command = new SqlCommand(sqlCount, connect, tran);
            soLuong = (int)command.ExecuteScalar();
            if (run == "Unrepeatable read error" || run == "Unrepeatable read fix")
            {
                Thread.Sleep(10000);
            }
            command = new SqlCommand(sqlSelect, connect, tran);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            tran.Commit();
            connect.Close();
            
            List<SimDTO> listSimDTO = new List<SimDTO>();
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    SimDTO simDTO = new SimDTO();
                    simDTO.MaSanPham = int.Parse(dataRow["MaSanPham"].ToString());
                    simDTO.SoThueBao = dataRow["SoThueBao"].ToString();
                    simDTO.SoSeri = dataRow["SoSeri"].ToString();
                    simDTO.DonGia = float.Parse(dataRow["DonGia"].ToString());
                    simDTO.MaNhaMang = int.Parse(dataRow["MaNhaMang"].ToString());
                    listSimDTO.Add(simDTO);
                }
            }
            else
            {
                listSimDTO = null;
            }
            return listSimDTO;
        }

        public static List<SimDTO> DanhSachSim()
        {
            DataTable dataTable = DataProvider.ExecuteReader("usp_DanhSachSim");
            List<SimDTO> listSimDTO = new List<SimDTO>();
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    SimDTO simDTO = new SimDTO();
                    simDTO.MaSanPham = (int)dataRow["MaSanPham"];
                    simDTO.SoThueBao = dataRow["SoThueBao"].ToString(); ;
                    simDTO.SoSeri = dataRow["SoSeri"].ToString();
                    simDTO.DonGia = float.Parse(dataRow["DonGia"].ToString()) ;
                    simDTO.MaNhaMang = (int)dataRow["MaNhaMang"];
                    simDTO.TinhTrang = dataRow["TinhTrang"].ToString();
                    listSimDTO.Add(simDTO);
                }
            }
            else
            {
                listSimDTO = null;
            }
            return listSimDTO;
        }


        public static SimDTO ThongTinSim (int maSanPham)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@MaSanPham", maSanPham));
            DataTable dataTable = DataProvider.ExecuteReader("usp_ThongTinSim", sqlParamas);
            SimDTO simDTO = new SimDTO();
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                simDTO.MaSanPham = (int)dataRow["MaSanPham"];
                simDTO.SoThueBao = dataRow["SoThueBao"].ToString(); ;
                simDTO.SoSeri = dataRow["SoSeri"].ToString();
                simDTO.DonGia = float.Parse(dataRow["DonGia"].ToString());
                simDTO.MaNhaMang = (int)dataRow["MaNhaMang"];
                simDTO.TinhTrang = dataRow["TinhTrang"].ToString();

            }
            else
            {
                simDTO = null;
            }
            return simDTO;
        }

        public static bool ThemSim (SimDTO simDTO, int maLoaiSim)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@SoThueBao", simDTO.SoThueBao));
            sqlParamas.Add(new SqlParameter("@SoSeri", simDTO.SoSeri));
            sqlParamas.Add(new SqlParameter("@DonGia", simDTO.DonGia));
            sqlParamas.Add(new SqlParameter("@MaLoaiSim", maLoaiSim));
            sqlParamas.Add(new SqlParameter("@MaNhaMang", simDTO.MaNhaMang));
            return DataProvider.ExecuteNoneQuery("usp_ThemSim", sqlParamas);
        }

        public static int CapNhatSim(SimDTO simDTO)
        {
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);

            connect.Open();
            SqlTransaction tran = connect.BeginTransaction(IsolationLevel.ReadCommitted);

            //Kiểm tra sản phẩm có tồn tại hay không
            string sql = "select count(*) from Sim where MaSanPham = " + simDTO.MaSanPham.ToString();
            SqlCommand command = new SqlCommand(sql, connect, tran);
            if ((int)command.ExecuteScalar() < 1)
            {
                tran.Rollback();
                connect.Close();
                return 1;
            }

            //Cập nhật sim
            sql = "update Sim set SoThueBao = '" + simDTO.SoThueBao + "', ";
            sql += "  SoSeri = '" + simDTO.SoSeri + "' ";
            sql += " where MaSanPham =  " + simDTO.MaSanPham.ToString();
            command = new SqlCommand(sql, connect, tran);

            if (command.ExecuteNonQuery() <= 0)
            {
                tran.Rollback();
                connect.Close();
                return 1;
            }

            //Kiểm tra số lượng sim
            sql = "select count(MaSanPham) from Sim  where SoThueBao like '" + simDTO.SoThueBao + "' and SoSeri like  '" + simDTO.SoSeri + "' ";
            command = new SqlCommand(sql, connect, tran);
            int soluong = (int)command.ExecuteScalar();
            if (soluong > 1)
            {
                tran.Rollback();
                connect.Close();
                return 2;
            }

            //cập nhật sản phẩm
            sql = "update SanPham set MaNhaMang = " + simDTO.MaNhaMang + ", ";
            sql += " DonGia = " + simDTO.DonGia + ", ";
            sql += " TinhTrang = N'" + simDTO.TinhTrang +"' ";
            sql += " where MaSanPham =  " + simDTO.MaSanPham.ToString();
            command = new SqlCommand(sql, connect, tran);
            if (command.ExecuteNonQuery() <= 0)
            {
                tran.Rollback();
                connect.Close();
                return 1;
            }
          
            /*
            //Kiểm tra cập nhật số thuê bao
            if (simDTO.SoThueBao != "")
            {
                sql = "update Sim set SoThueBao = " + simDTO.SoThueBao + " where MaSanPham =  " + simDTO.MaSanPham.ToString();
                command = new SqlCommand(sql, connect, tran);
                int soLuong = (int)command.ExecuteScalar();
                if (soLuong > 1)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
            }

            //Kiểm tra cập nhật số seri
            if (simDTO.SoSeri != "")
            {
                sql = "update Sim set SoSeri = " + simDTO.SoSeri + " where MaSanPham =  " + simDTO.MaSanPham.ToString();
                command = new SqlCommand(sql, connect, tran);
                if (command.ExecuteNonQuery() == 0)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
            }

            //Kiểm tra số lượng sim
            if(simDTO.SoThueBao != "" && simDTO.SoSeri != "")
            {
                sql = "select count(MaSanPham) from Sim  where SoThueBao like " + simDTO.SoThueBao + " and SoSeri like  " + simDTO.SoSeri;
                command = new SqlCommand(sql, connect, tran);
                if (command.ExecuteNonQuery() == 0)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
                
            }

            //Kiểm tra cập nhật đơn giá
            if (simDTO.DonGia != -1)
            {
                sql = "update SanPham set DonGia = " + simDTO.DonGia+ " where MaSanPham =  " + simDTO.MaSanPham.ToString();
                command = new SqlCommand(sql, connect, tran);
                if (command.ExecuteNonQuery() == 0)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
            }

            //Kiểm tra cập nhật nhà mạng
            if (simDTO.MaNhaMang != -1)
            {
                sql = "update SanPham set MaNhaMang = " + simDTO.MaNhaMang + " where MaSanPham =  " + simDTO.MaSanPham.ToString();
                command = new SqlCommand(sql, connect, tran);
                if (command.ExecuteNonQuery() == 0)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
            }

            //Kiểm tra cập nhật tình trạng
            if (simDTO.TinhTrang != "")
            {
                sql = "update SanPham set TinhTrang = " + simDTO.TinhTrang+ " where MaSanPham =  " + simDTO.MaSanPham.ToString();
                command = new SqlCommand(sql, connect, tran);
                if (command.ExecuteNonQuery() == 0)
                {
                    tran.Rollback();
                    connect.Close();
                    return false;
                }
            }
            */
            tran.Commit();
            connect.Close();
            return 0;
        }

    }
}
